"""
__project__ = '17lebohomework'

__file_name__ = '练习1'

__author__ = 'Administrator'

__time__ = '2021/4/25 23:19'

__product_name__ = PyCharm

"""

import pymysql  # 导入pymysql工具


def create_table():
    # 创建连接
    conn = pymysql.connect(user='user', password="leboAa!#$123", host='49.233.39.160', database='lebo16', port=3306,
                           charset="utf8")
    # 获取游标
    cursor = conn.cursor()
    # 用游标的execute方法去执行MySQL语句
    cursor.execute('drop table if exists students_xdz')
    sql = 'create table students_xdz(studentNo int auto_increment primary key, ' \
          'name varchar(10), ' \
          'sex varchar(1), ' \
          'hometown varchar(20), ' \
          'age tinyint(4), ' \
          'class varchar(10), ' \
          'card varchar(20))'
    try:
        cursor.execute(sql)
        print('数据表创建成功')
    except Exception as e:
        print('数据库创建失败的原因：%s' % e)
    finally:
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()


def insert_table():
    conn = pymysql.connect(user='user', password="leboAa!#$123", host='49.233.39.160', database='lebo16', port=3306,
                           charset="utf8")
    cursor1 = conn.cursor()
    sql = """insert into students_xdz values 
('001', '王昭君', '女', '北京', '20', '1班', '340322199001247654'), 
('002', '诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),
('003', '张飞', '男', '南京', '24', '3班', '340322199003247654'),
('004', '白起', '男', '安徽', '22', '4班', '340322199005247654'),
('005', '大乔', '女', '天津', '19', '3班', '340322199004247654'),
('006', '孙尚香', '女', '河北', '18', '1班', '340322199006247654'),
('007', '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),
('008', '小乔', '女', '河南', '15', '3班', null),
('009', '百里守约', '男', '湖南', '21', '1班', ''),
('010', '妲己', '女', '广东', '26', '2班', '340322199607247654'),
('011', '李白', '男', '北京', '30', '4班', '340322199005267754'),
('012', '孙膑', '男', '新疆', '26', '3班', '340322199000297655')"""
    try:
        cursor1.execute(sql)
        # 事务：进行提交
        conn.commit()
        print('数据插入成功')
    except Exception as e:
        # 事务：失败回滚
        conn.rollback()
        print('数据库插入失败的原因：%s' % e)
    finally:
        # 关闭游标
        cursor1.close()
        # 关闭连接
        conn.close()



# create_table()

insert_table()
